import FileHandler
import psycopg2


def special_num(num):
    if float(num) > 900000:
        return "null"
    else:
        return num


def special_num_str(num):
    if float(num) >  900000:
        return "null"
    else:
        return "'" + num + "'"

def special_date_str(num):
    if len(num) > 1:
        return "'" + num + "'"
    else:
        return "'0" + num + "'"


def special_num_int(num):
    num = int(float(num))
    if float(num) > 900000:
        return "null"
    else:
        return "'" + str(num) + "'"


def import_data():
    # files = FileHandler.FileHandler.dir_has_file(r"G:\工作文档\气象局\数据对接\河南5-7月数据\河南5-7月数据\区域站1", "txt")
    files = FileHandler.FileHandler.dir_has_file(r"G:\工作文档\气象局\河南数据7.17-8.31\区域站", "txt")
    # files = FileHandler.FileHandler.dir_has_file(r"G:\工作文档\气象局\河南数据7.17-8.31\国家站", "txt")
    # conn = psycopg2.connect(database="meteorological", user="postgres", password="postgres", host="127.0.0.1",
    #                         port="5432")
    conn = psycopg2.connect(database="db_weather", user="postgres", password="postgresql", host="192.168.1.71",
                            port="5432")
    cursor = conn.cursor()

    for file_path in files:
        try:
            f = open(file_path, mode='r', encoding='utf-8')
            print(file_path)
            next(f)
            next(f)
            line = f.readline()
            while line:
                data = line.split()
                # data.insert(5,'14')
                print("长度",len(data))
                print(data)
                sql = (
                        "INSERT INTO station_data_day(" + "station_name, station_num, lat, lon, alti, station_level, year, month, day, tem_avg, tem_avg_q, tem_max, tem_max_q, tem_min, tem_min_q, rhu_avg, rhu_avg_q, rhu_min, rhu_min_q, win_s_max, win_s_max_q, win_s_inst_max, win_s_inst_max_q, ssh, ssh_q, pre_time_2020, pre_time_2020_q)" +
                        "VALUES ('" + data[0] + "','" + data[1] + "'," + data[2] + "," + data[3] + "," + data[
                            4] + "," + special_num_int(data[5]) + ",'" + data[6] + "','" + data[7] + "','" + data[
                            8] + "'," + special_num(data[9]) + "," + special_num_str(
                    data[10]) + "," + special_num(data[
                                                      11]) + "," + special_num_str(data[12]) + "," + special_num(
                    data[13]) + "," + special_num_str(data[14]) + "," + special_num(data[15]) + "," + special_num_str(
                    data[16]) + "," + special_num(data[17]) + "," + special_num_str(
                    data[18]) + "," + special_num(data[21]) + "," + special_num_str(data[22]) + "," + special_num(
                    data[23]) + "," + special_num_str(data[24]) + "," + special_num(data[25]) + "," + special_num_str(
                    data[26]) + "," + special_num(data[19]) + "," + special_num_str(
                    data[20]) + ")")
                print(sql)
                cursor.execute(sql)
                line = f.readline()
        except UnicodeDecodeError as err:
            print(err)

    conn.commit()
    conn.close()


def import_other():
    # files = FileHandler.FileHandler.dir_has_file(r"G:\工作文档\气象局\国家站数据补充\国家站数据补充", "txt")
    files = FileHandler.FileHandler.dir_has_file(r"G:\工作文档\气象局\河南数据7.17-8.31\国家站", "txt")
    conn = psycopg2.connect(database="meteorological", user="postgres", password="postgres", host="127.0.0.1",
                            port="5432")
    # conn = psycopg2.connect(database="db_weather", user="postgres", password="postgresql", host="192.168.1.71",
    #                         port="5432")
    cursor = conn.cursor()

    for file_path in files:
        print(file_path)
        try:
            f = open(file_path, mode='r', encoding='utf-8')
            print(file_path)
            next(f)
            next(f)
            line = f.readline()
            while line:
                data = line.split()

                print(len(data))
                sql = (
                        "update station_data_day set "
                        "gst_avg=" + special_num(data[8]) + ", "
                                                            "gst_avg_q=" + special_num_str(data[9]) + ","
                                                                                                       "gst_max=" + special_num(
                    data[10]) + ", "
                                "gst_max_q=" + special_num_str(
                    data[11]) + ", "
                                "gst_min=" + special_num(data[12]) + ","
                                                                     "gst_min_q=" + special_num_str(data[13]) + ","
                                                                                                                 "ice=" + special_num_str(
                    data[14]) + ", "
                                "ice_q=" + special_num_str(data[15]) + ", " + "hail=" + special_num_str(
                    data[16]) + ", "
                                "hail_q=" + special_num_str(data[17]) + ", " + "thund=" + special_num_str(
                    data[18]) + ", "+ "lit=" + special_num_str(
                    data[20]) + ", "+
                                "lit_q=" + special_num_str(data[21]) + ", "+
                                "thund_q=" + special_num_str(data[19]) + ", "+"snow_depth=" + special_num(data[22]) + ", "
                                                            "snow_depth_q=" + special_num_str(data[23]) + ","+"vis_min=" + special_num(data[24]) + ", "
                                                            "vis_min_q=" + special_num_str(data[25]) + ","+ "haze=" + special_num_str(
                    data[26]) + ", "+
                                "haze_q=" + special_num_str(data[27]) + ", "+"rex_max_1h=" + special_num(data[28]) + ", "
                                                            "rex_max_1h_q=" + special_num_str(data[29]) + ","+ "fog=" + special_num_str(
                    data[30]) + ", "+
                                "fog_q=" + special_num_str(data[31]) + ", "+ "sast=" + special_num_str(
                    data[32]) + ", "+
                                "sast_q=" + special_num_str(data[33]) + ""

                        + "  where station_num='" + data[1] + "'  and year='" + data[5] + "' and (month=" + special_date_str(data[
                            6]) + " or month ='"+data[6]+"' )and (day='" + data[7] + "' or day="+special_date_str(data[
                            7])+" )")
                print(sql)
                cursor.execute(sql)
                line = f.readline()
        except UnicodeDecodeError as err:
            print(err)

    conn.commit()
    conn.close()


def import_other_all():
    # files = FileHandler.FileHandler.dir_has_file(r"G:\工作文档\气象局\国家站数据补充\国家站数据补充", "txt")
    files = FileHandler.FileHandler.dir_has_file(r"G:\工作文档\气象局\河南数据7.17-8.31\国家站", "txt")
    # conn = psycopg2.connect(database="meteorological", user="postgres", password="postgres", host="127.0.0.1",
    #                         port="5432")
    conn = psycopg2.connect(database="db_weather", user="postgres", password="postgresql", host="192.168.1.71",
                            port="5432")
    cursor = conn.cursor()

    for file_path in files:
        print(file_path)
        try:
            f = open(file_path, mode='r', encoding='utf-8')
            print(file_path)
            next(f)
            next(f)
            line = f.readline()
            while line:
                data = line.split()

                print(len(data))
                sql = (
                        "update station_data_day set "
                        "gst_avg=" + special_num(data[27]) + ", "
                                                            "gst_avg_q=" + special_num_str(data[28]) + ","
                                                                                                       "gst_max=" + special_num(
                    data[29]) + ", "
                                "gst_max_q=" + special_num_str(
                    data[30]) + ", "
                                "gst_min=" + special_num(data[31]) + ","
                                                                     "gst_min_q=" + special_num_str(data[32]) + ","
                                                                                                                 "ice=" + special_num_str(
                    data[33]) + ", "
                                "ice_q=" + special_num_str(data[34]) + ", " + "hail=" + special_num_str(
                    data[35]) + ", "
                                "hail_q=" + special_num_str(data[36]) + ", " + "thund=" + special_num_str(
                    data[37]) + ", "+ "lit=" + special_num_str(
                    data[39]) + ", "+
                                "lit_q=" + special_num_str(data[40]) + ", "+
                                "thund_q=" + special_num_str(data[38]) + ", "+"snow_depth=" + special_num(data[41]) + ", "
                                                            "snow_depth_q=" + special_num_str(data[42]) + ","+"vis_min=" + special_num(data[43]) + ", "
                                                            "vis_min_q=" + special_num_str(data[44]) + ","+ "haze=" + special_num_str(
                    data[45]) + ", "+
                                "haze_q=" + special_num_str(data[46]) + ", "+"rex_max_1h=" + special_num(data[47]) + ", "
                                                            "rex_max_1h_q=" + special_num_str(data[48]) + ","+ "fog=" + special_num_str(
                    data[49]) + ", "+
                                "fog_q=" + special_num_str(data[50]) + ", "+ "sast=" + special_num_str(
                    data[51]) + ", "+
                                "sast_q=" + special_num_str(data[52]) + ""

                        + "  where station_num='" + data[1] + "'  and year='" + data[5] + "' and (month=" + special_date_str(data[
                            6]) + " or month ='"+data[6]+"' )and (day='" + data[7] + "' or day="+special_date_str(data[
                            7])+" )")
                print(sql)
                cursor.execute(sql)
                line = f.readline()
        except UnicodeDecodeError as err:
            print(err)

    conn.commit()
    conn.close()


if __name__ == "__main__":
    path = r"G:\工作文档\气象局\河南省站点数据"
    # path = r"D:\河南省站点数据"
    # FileHandler.FileHandler.translateEncode(path, r"G:\工作文档\气象局\河南省站点数据转码", "utf-8")
    import_data()
    # import_other()
    # import_other_all()
    print("入库完成")

    # update
    # station_data_day
    # set
    # time_monitor = to_date(concat(year, '-', month, '-', day), 'yyyy-MM-dd')
    # where
    # time_monitor is null
    #
    # --update
    # station_data_day
    # set
    # month = concat('0', month)
    # where
    # time_monitor is null and "char_length"(month) = 1;
    # --update
    # station_data_day
    # set
    # day = concat('0', day)
    # where
    # time_monitor is null and "char_length"(day) = 1
